/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Implementation.SourceCode.DAL;

import Implementation.SourceCode.entities.diengiai;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author mrkingdom75
 */
public class DB_Access {
    Connection conn;
    public DB_Access(){}
    
    public Connection getConnection(boolean isAutoCommit){
        try{
            Class.forName("org.sqlite.JDBC");
            conn = DriverManager.getConnection("jdbc:sqlite:Database/baocao.db");
            conn.setAutoCommit(isAutoCommit);
        }catch(Exception ex){
            ex.printStackTrace();
        }
        return conn;
    }
    
    public String lastID(String table){
        String maxID = "1";
        try{
            conn = getConnection(true);
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("select max(id) as max from "+table);
            if(rs.getString("max")!=null){
                maxID = Integer.parseInt(rs.getString("max"))+1+"";
            }
            rs.close(); 
        }catch(Exception ex){
            ex.printStackTrace();
        }
        return maxID;
    }

    public boolean kiemTraThuTu(String thutu) {
        boolean result = false;
        try{
            conn = getConnection(true);
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("select id from viettat where ten_viet_tat ='"+thutu+"'");
            String id = "";
            if(rs.next()){
                id = rs.getString("id");
            }
            if(!id.isEmpty()){
                result =  true;
            }
            rs.close(); 
        }catch(Exception ex){
            ex.printStackTrace();
        }
        return result;
    }

    public String layTenDayDu(String tenVietTat) {
        String retVal = "";
        try{
            conn = getConnection(true);
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("select ten_day_du from viettat where ten_viet_tat ='"+tenVietTat+"'");
            if(rs.next()){
                retVal = rs.getString("ten_day_du");
            }
            rs.close();
        }catch(Exception ex){
            ex.printStackTrace();
        }
        return retVal;
    }

    public boolean updateTableData(String table, String id, String updateValue, String colName) {
        boolean result = false;
        try{
            String update = "update "+table+" set "+colName+" = '"+updateValue+"' where id = '"+id+"'";
            conn = getConnection(true);
            Statement stmt = conn.createStatement();
            if(stmt.executeUpdate(update)==1){
                result=true;
            }
        }catch(Exception ex){
            ex.printStackTrace();
        }
        return result;
    }

    public void deleteRowById(String table, String id) {
        try {
            String delete = "delete from "+table+" where id='"+id+"'";
            conn = getConnection(true);
            Statement stmt = conn.createStatement();
            stmt.execute(delete);
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
    
    public Vector<diengiai> getDataOn(int ngay, int thang) {
        Vector<diengiai> lstDienGiai = new Vector<diengiai>();
        try {
            conn = getConnection(true);
            Statement stmt = conn.createStatement();
            String query = "select dg.id, ngay, thang, hoa_don, dien_giai, "
                    + "so_tien, vt_thu_tu, ten_day_du, ten_viet_tat from diengiai dg, viettat vt where dg.thu_tu = vt.ten_viet_tat and %s = thang and ngay = %s order by vt_thu_tu, ten_viet_tat, thang, ngay;";
            query = String.format(query , thang+"", ngay+"" );
            ResultSet rs = stmt.executeQuery(query);
            while(rs.next()){
                diengiai dg = new diengiai();
                dg.setId(rs.getInt("id"));
                dg.setNgay(rs.getInt("ngay"));
                dg.setThang(rs.getInt("thang"));
                dg.setHoadon(rs.getInt("hoa_don"));
                dg.setDiengiai(rs.getString("dien_giai"));;
                dg.setSotien(rs.getLong("so_tien"));
                dg.setThutusheet(rs.getInt("vt_thu_tu"));
                dg.setTendaydu(rs.getString("ten_day_du"));
                dg.setThutu(rs.getString("ten_viet_tat"));
                lstDienGiai.add(dg);
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return lstDienGiai;
    }


    public Vector<diengiai> getDataFromTo(int ngayFrom, int thangFrom, int ngayTo, int thangTo) {
        Vector<diengiai> lstDienGiai = new Vector<diengiai>();
        try {
            conn = getConnection(true);
            Statement stmt = conn.createStatement();

            String query = "select dg.id, ngay, thang, hoa_don, dien_giai, "
                    + "so_tien, vt_thu_tu, ten_day_du, ten_viet_tat from diengiai dg, viettat vt where dg.thu_tu = vt.ten_viet_tat and (%s <= thang and thang <= %s) and (%s <= ngay and ngay <= %s) order by vt_thu_tu, ten_viet_tat, thang, ngay;";
            query = String.format(query , thangFrom+"", thangTo+"", ngayFrom+"", ngayTo+"");
            ResultSet rs = stmt.executeQuery(query);
            while(rs.next()){
                diengiai dg = new diengiai();
                dg.setId(rs.getInt("id"));
                dg.setNgay(rs.getInt("ngay"));
                dg.setThang(rs.getInt("thang"));
                dg.setHoadon(rs.getInt("hoa_don"));
                dg.setDiengiai(rs.getString("dien_giai"));;
                dg.setSotien(rs.getLong("so_tien"));
                dg.setThutusheet(rs.getInt("vt_thu_tu"));
                dg.setTendaydu(rs.getString("ten_day_du"));
                dg.setThutu(rs.getString("ten_viet_tat"));
                lstDienGiai.add(dg);
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return lstDienGiai;
    }

    public Vector<diengiai> ghiTongTienTungNgay(int thang) {
        Vector<diengiai> lstDienGiai = new Vector<diengiai>();
        try{
            conn = getConnection(true);
            Statement stmt = conn.createStatement();

            String query = "select so_tien, ngay, thang , nam from diengiai where thang = %s order by ngay;";
            ResultSet rs = stmt.executeQuery(String.format(query,thang+""));
            while(rs.next()){
                diengiai dg = new diengiai();
                dg.setNgay(rs.getInt("ngay"));
                dg.setThang(rs.getInt("thang"));
                dg.setNam(rs.getInt("nam"));
                dg.setSotien(rs.getLong("so_tien"));
                lstDienGiai.add(dg);
            }
        }catch(Exception ex){
            ex.printStackTrace();
        }
        return lstDienGiai;
    }


    public Vector<diengiai> loadTongTienTheoNgay(int ngay, int thang, int nam) {
        Vector<diengiai> lstDienGiai = new Vector<diengiai>();
        try{
            conn = getConnection(true);
            Statement stmt = conn.createStatement();
            String query = "select so_tien, ngay, thang , nam from diengiai where ngay = %s and thang = %s and nam = %s;";
            query = String.format(query, ngay+"", thang+"", nam+"");
            ResultSet rs = stmt.executeQuery(query);
            while(rs.next()){
                diengiai dg = new diengiai();
                dg.setNgay(rs.getInt("ngay"));
                dg.setThang(rs.getInt("thang"));
                dg.setNam(rs.getInt("nam"));
                dg.setSotien(rs.getLong("so_tien"));
                lstDienGiai.add(dg);
            }
        }catch(Exception ex){
            ex.printStackTrace();
        }
        return lstDienGiai;
    }

    public int deleteDataByMonth(int month) throws Exception{
        try{
            int retVal = 0;
            conn = getConnection(true);
            Statement stmt = conn.createStatement();
            retVal = stmt.executeUpdate("delete from diengiai where thang = "+month);
            return retVal;
        }catch(Exception ex){
            throw ex;
        }
    }
}
